package com.cho;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.cho.db.dao.TableDao;

public class ExcelCore {
	
	private Logger logger =  Logger.getLogger(this.getClass());
	
	private TableDao dao = new TableDao();
	private final static String EXCEL_PATH = "result/";

	/**
	 * @param args
	 * @throws IOException 
	 */
	public static void main(String[] args) throws IOException {
		// TODO Auto-generated method stub
		ExcelCore t = new ExcelCore();
		
		t.fileAndTableList("TEST2");
		t.tableCRUD("TEST2");
	}
	
	public void makeExcel(String projectName) throws IOException{
		fileAndTableList(projectName);
		tableCRUD(projectName);
	}
    
    public void fileAndTableList(String projectName) throws IOException{
	    Workbook wb = new XSSFWorkbook();
	    
	    Sheet sheet = wb.createSheet("목록");
	    Row row = sheet.createRow(0);
	    createTitleCell(wb, row, (short) 0,"파일경로", CellStyle.ALIGN_CENTER);
	    createTitleCell(wb, row, (short) 1,"파일명", CellStyle.ALIGN_CENTER);
	    createTitleCell(wb, row, (short) 2,"테이블명", CellStyle.ALIGN_CENTER);
	    createTitleCell(wb, row, (short) 3,"CRUD", CellStyle.ALIGN_CENTER);
	    createTitleCell(wb, row, (short) 4,"소스 라인", CellStyle.ALIGN_CENTER);

	    sheet.setColumnWidth(0, (1000 * 25));
	    sheet.setColumnWidth(1, (1000 * 4));
	    sheet.setColumnWidth(2, (1000 * 7));
	    
	    List<Map<String, String>> list = dao.selectFileAndTableList(projectName);
	    int listCount = list.size();
	    
	    for (int i = 0; i < listCount; i++) {
	    	Map<String, String> data = list.get(i);
		    row = sheet.createRow(i + 1);
		    createTextCell(wb, row, (short) 0, data.get("FILE_PATH"), CellStyle.ALIGN_LEFT);
		    createTextCell(wb, row, (short) 1, data.get("FILE_NAME"), CellStyle.ALIGN_LEFT);
		    createTextCell(wb, row, (short) 2, data.get("TABLE_NAME"), CellStyle.ALIGN_LEFT);
		    createTextCell(wb, row, (short) 3, data.get("CRUD_TYPE"), CellStyle.ALIGN_CENTER);
		    createTextCell(wb, row, (short) 4, data.get("SOURCE_LINE"), CellStyle.ALIGN_RIGHT);
		}
	    
	    FileOutputStream fileOut = new FileOutputStream(EXCEL_PATH + projectName + "_파일별테이블목록.xlsx");
	    wb.write(fileOut);
	    fileOut.close();
	    
	    logger.info("엑셀파일 생성 완료 ==>>> 파일명: " + EXCEL_PATH + projectName+"_파일별테이블목록.xlsx");
    }
    
    public void tableCRUD(String projectName) throws IOException{
    	Workbook wb = new XSSFWorkbook();
    	Sheet sheet = wb.createSheet("목록");
    	
    	sheet.setColumnWidth(0, (1000 * 5));
    	sheet.setColumnWidth(1, (1000 * 5));
    	
    	Row row = null;
	    List<Map<String, String>> tableList = dao.selectTableList(projectName);
	    int colCount = tableList.size();
	    
	    
	    List<Map<String, String>> fileList = dao.selectFileList(projectName);
	    int rowCount = fileList.size() + 2;//0, 1 row�� �ϵ��ڵ��̶� + 2 ��.
	    
	    
	    List<Map<String, String>> crudList = dao.selectTableCRUDList(projectName);
	    int curdCount = crudList.size();
	    
	    Map<String, Integer> tableCrudCount = new HashMap<String, Integer>(); 
	    
	    for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
	    	int createCountUseFile = 0;
	    	int readCountUseFile = 0;
	    	int updateCountUseFile = 0;
	    	int deleteCountUseFile = 0;
	    	
	    	row = sheet.createRow(rowIndex);
	    	row.setHeight((short)750);
	    	
	    	for (int colIndex = 0; colIndex < colCount; colIndex++) {
		    	Map<String, String> tableListData = tableList.get(colIndex);
		    	String tableName = tableListData.get("TABLE_NAME");
		    	
		    	if(rowIndex == 0){
		    		createTitleCell(wb, row, (short) 0, "구분", CellStyle.ALIGN_CENTER);
		    		createTitleCell(wb, row, (short) 1, "테이블명", CellStyle.ALIGN_CENTER);
		    		createTitleCell(wb, row, (short) (colIndex + 2), tableName, CellStyle.ALIGN_LEFT);
		    		sheet.setColumnWidth(colIndex + 2, (1000 * 5));
		    		tableCrudCount.put(tableName + "_C", 0);
		    		tableCrudCount.put(tableName + "_R", 0);
		    		tableCrudCount.put(tableName + "_U", 0);
		    		tableCrudCount.put(tableName + "_D", 0);
		    	}
		    	
		    	if(rowIndex == 1){
		    		createTitleCell(wb, row, (short) 0, "파일명", CellStyle.ALIGN_CENTER);
		    		createTitleCell(wb, row, (short) 1, "COUNT", CellStyle.ALIGN_CENTER);
		    	}
		    	
		    	if(rowIndex >= 2){
			    	Map<String, String> fileListData = fileList.get(rowIndex - 2);
			    	createTitleCell(wb, row, (short) 0, fileListData.get("FILE_NAME"), CellStyle.ALIGN_LEFT);
				    
				    String crud = "";
				    
				    for (int crudIndex = 0; crudIndex < curdCount; crudIndex++) {
				    	Map<String, String> crudListData = crudList.get(crudIndex);
				    	
				    	if(fileListData.get("FILE_SEQ").equals(crudListData.get("FILE_SEQ"))
				    		&& tableName.equals(crudListData.get("TABLE_NAME"))){
				    		String crudType = crudListData.get("CRUD_TYPE");
				    		crud += crudType;
				    		
				    		if(crudType.equals("C")){
				    			createCountUseFile++;
				    			tableCrudCount.put(tableName + "_C", tableCrudCount.get(tableName + "_C") + 1);
				    		}else if(crudType.equals("R")){
				    			readCountUseFile++;
				    			tableCrudCount.put(tableName + "_R", tableCrudCount.get(tableName + "_R") + 1);
				    		}else if(crudType.equals("U")){
				    			updateCountUseFile++;
				    			tableCrudCount.put(tableName + "_U", tableCrudCount.get(tableName + "_U") + 1);
				    		}else if(crudType.equals("D")){
				    			deleteCountUseFile++;
				    			tableCrudCount.put(tableName + "_D", tableCrudCount.get(tableName + "_D") + 1);
				    		}
				    	}
					}
				    createTextCell(wb, row, (short) (colIndex + 2), crud, CellStyle.ALIGN_CENTER);
		    	}
		    	
	    		if(rowIndex == rowCount - 1){
	    			createTitleCell(wb, sheet.getRow(1), (short) (colIndex + 2), 
	    					"C("+tableCrudCount.get(tableName + "_C")+")R("+tableCrudCount.get(tableName + "_R")+")\n" +
	    							"U("+tableCrudCount.get(tableName + "_U")+")D("+tableCrudCount.get(tableName + "_D")+")", 
	    							CellStyle.ALIGN_CENTER);
	    		}
			}
	    	if(rowIndex >= 2){
	    		createTitleCell(wb, row, (short) 1, 
	    				"C("+createCountUseFile+")R("+readCountUseFile+")\n" +
	    						"U("+updateCountUseFile+")D("+deleteCountUseFile+")", 
	    						CellStyle.ALIGN_CENTER);
	    	}
		}
	    
	    
    	FileOutputStream fileOut = new FileOutputStream(EXCEL_PATH + projectName+"_CRUD_매트릭스.xlsx");
    	wb.write(fileOut);
    	fileOut.close();
    	logger.info("엑셀파일 생성 완료 ==>>> 파일명: " + EXCEL_PATH + projectName+"_CRUD_매트릭스.xlsx");
    }

    private void createTitleCell(Workbook wb, Row row, short column, String value, short halign) {
    	createCell(wb, row, column, value, halign, IndexedColors.YELLOW.getIndex(), CellStyle.SOLID_FOREGROUND, true);
    }
    
    private void createTextCell(Workbook wb, Row row, short column, String value, short halign) {
    	createCell(wb, row, column, value, halign, IndexedColors.WHITE.getIndex(), CellStyle.SOLID_FOREGROUND, false);
    }
	
	private void createCell(Workbook wb, Row row, short column, String value, short halign, short fillForegroundColor, short fillPattern, boolean wrapText) {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setFillForegroundColor(fillForegroundColor);
        cellStyle.setFillPattern(fillPattern);
        cellStyle.setWrapText(wrapText);
        cell.setCellStyle(cellStyle);
    }
}
